##############################################################################
#
# Zope Public License (ZPL) Version 1.0
# -------------------------------------
#
# Copyright (c) Digital Creations.  All rights reserved.
#
# This license has been certified as Open Source(tm).
#
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions are
# met:
#
# 1. Redistributions in source code must retain the above copyright
#    notice, this list of conditions, and the following disclaimer.
#
# 2. Redistributions in binary form must reproduce the above copyright
#    notice, this list of conditions, and the following disclaimer in
#    the documentation and/or other materials provided with the
#    distribution.
#
# 3. Digital Creations requests that attribution be given to Zope
#    in any manner possible. Zope includes a "Powered by Zope"
#    button that is installed by default. While it is not a license
#    violation to remove this button, it is requested that the
#    attribution remain. A significant investment has been put
#    into Zope, and this effort will continue if the Zope community
#    continues to grow. This is one way to assure that growth.
#
# 4. All advertising materials and documentation mentioning
#    features derived from or use of this software must display
#    the following acknowledgement:
#
#      "This product includes software developed by Digital Creations
#      for use in the Z Object Publishing Environment
#      (http://www.zope.org/)."
#
#    In the event that the product being advertised includes an
#    intact Zope distribution (with copyright and license included)
#    then this clause is waived.
#
# 5. Names associated with Zope or Digital Creations must not be used to
#    endorse or promote products derived from this software without
#    prior written permission from Digital Creations.
#
# 6. Modified redistributions of any form whatsoever must retain
#    the following acknowledgment:
#
#      "This product includes software developed by Digital Creations
#      for use in the Z Object Publishing Environment
#      (http://www.zope.org/)."
#
#    Intact (re-)distributions of any official Zope release do not
#    require an external acknowledgement.
#
# 7. Modifications are encouraged but must be packaged separately as
#    patches to official Zope releases.  Distributions that do not
#    clearly separate the patches from the original work must be clearly
#    labeled as unofficial distributions.  Modifications which do not
#    carry the name Zope may be packaged in any form, as long as they
#    conform to all of the clauses above.
#
#
# Disclaimer
#
#   THIS SOFTWARE IS PROVIDED BY DIGITAL CREATIONS ``AS IS'' AND ANY
#   EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
#   IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
#   PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL DIGITAL CREATIONS OR ITS
#   CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
#   SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
#   LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
#   USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
#   ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
#   OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
#   OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
#   SUCH DAMAGE.
#
#
# This software consists of contributions made by Digital Creations and
# many individuals on behalf of Digital Creations.  Specific
# attributions are listed in the accompanying credits file.
#
##############################################################################
__doc__='''Database Connection

$Id: DABase.py 16 2006-11-24 14:28:11Z pigletto $'''
__version__='$Revision: 1.7 $'[11:-2]

import Shared.DC.ZRDB.Connection, sys
from Globals import HTMLFile
from ExtensionClass import Base
import Acquisition
import Globals
from App.Dialogs import MessageDialog
from cgi import escape

import logging
logger = logging.getLogger('DCOracle2 - DABase.py')

from DCOracle2 import dco2
from db import ORA_CONNECTION_ERRORS
import pool

class PooledConnection(Shared.DC.ZRDB.Connection.Connection):
    """ Base class that supports connection pooling
    """
    _isAnSQLConnection=1
    manage_properties = HTMLFile('dtml/connectionEdit', globals())
    
    def __init__(self, id, title, connection_string, poolsize, use_shared_pool=False, check=None):
        self.id                = str(id)
        self.title             = title
        self.conn_closed       = 0
        self.connection_string = connection_string
        self.poolsize          = self.parse_pool_size(poolsize)
        self.use_shared_pool   = use_shared_pool
        if check: 
            self.manage_open_connection()
            
    def manage_edit(self, title, connection_string, poolsize, \
                    use_shared_pool=False, check=None, 
                    REQUEST=None):
        """Change connection
        """
        self.edit(title, connection_string, poolsize, use_shared_pool, check)
        if REQUEST is not None:
            return MessageDialog(
                title='Edited',
                message='<strong>%s</strong> has been edited.' % escape(self.id),
                action ='./manage_main',
                )
    
    def manage_close_connection(self, REQUEST=None):
        """ Called when 'Close' button is pressed at ZMI
        """
        self.conn_closed = 1
        logger.info('manage_close_connection. Closing RM pool: \'%s\'' % (self.connection_string))
        pool.flushpool(self.connection_string, self.get_unique_id())
        
        if REQUEST is not None:
            return self.manage_main(self, REQUEST)

    def manage_open_connection(self, REQUEST=None):
        """ Called when 'Open' button is pressed at ZMI
        """
        self.conn_closed = 0
        logger.info('DABase.py manage_open_connection. RM pool opened: \'%s\''
                     % (self.connection_string))
        pool.getpool(self.connection_string, 
                     self.get_unique_id(), 
                     factory=self.factory, 
                     poolsize=self.get_pool_size())
        return self.manage_main(self, REQUEST)
        
    def edit(self, title, connection_string, poolsize=1, use_shared_pool=False, check=1):
        logger.info('DABase.py edit. Id: \'%s\'' % (self.connection_string))
        self.title = title
        p_size = self.parse_pool_size(poolsize)
        
        # we may need to close or flush pool if connection string
        # or pool size are changed
        if self.connection_string != connection_string:
            if not check:
                self.manage_close_connection()
            else:
                pool.flushpool(self.connection_string, self.get_unique_id())
            self.connection_string = connection_string
            self.use_shared_pool=use_shared_pool
        elif self.poolsize != p_size or self.use_shared_pool!=use_shared_pool:
            self.poolsize = p_size
            pool.flushpool(self.connection_string, self.get_unique_id())
            self.use_shared_pool=use_shared_pool
        
        if check: self.manage_open_connection()
            
    def __call__(self, v=None, REQUEST=None):
        return self.getconn()
        
    def parse_pool_size(self, poolsize):
        """ Check pool size
        """
        zserver_threads = self.getThreadsNo()
        try:
            if int(poolsize)>0 and int(poolsize)<=zserver_threads:
                return int(poolsize)
            return zserver_threads
        except:
            pass
        raise Exception('Poolsize must be a number!')
        
    def get_unique_id(self):
        if not hasattr(self, 'use_shared_pool'):
            self.use_shared_pool = True
        if self.use_shared_pool:
            return self.connection_string
        return 'zoracleda_%s' % (repr(self._p_oid))
        
    def get_pool_size(self):
        if self.use_shared_pool:
            return 4
        elif not hasattr(self, 'poolsize'):
            self.poolsize = self.getThreadsNo()
        return self.poolsize
        
    def connected(self):
        if pool.get_pool_size(self.connection_string, 
                              self.get_unique_id()) == 0:
            return 0
        return not getattr(self, 'conn_closed', 0)

    def getconn(self, create=True):
        if not hasattr(self, 'conn_closed'):
            setattr(self, 'conn_closed', 0)
        if self.conn_closed:
            raise Exception('Connection is closed!')
        conn = pool.getconn(self.connection_string,
                            self.get_unique_id(), 
                            create=create,
                            factory=self.factory, 
                            poolsize=self.get_pool_size())
        # check the connection state and if it is closed then try to reopen it
        # connection may be closed because of database error detected in 
        # db.py's: query, abort, rollback, _finish method.
        if not conn.isOpen():
            # last parameter is True and it says the pool to remove
            # conn from the pool
            logger.warning( '%s: getconn - connection is closed, trying to reopen connection' % (self.connection_string) )
            pool.putconn(self.connection_string, 
                         self.get_unique_id(), 
                         close=True)
            try:
                conn = pool.getconn(self.connection_string, 
                                    self.get_unique_id(), 
                                    create=create,
                                    factory=self.factory, 
                                    poolsize=self.get_pool_size())
            except Exception, e:
                logger.error( '%s: getconn - error' % (self.connection_string), exc_info=True )
                raise
        return conn

    def connect(self):
        if not hasattr(self, 'conn_closed'):
            setattr(self, 'conn_closed', 0)
        if not self.conn_closed:
            pool.getpool(self.connection_string,
                     self.get_unique_id(), 
                     create=True,
                     factory=self.factory, 
                     poolsize=self.get_pool_size())
                     
    def __setstate__(self, state):
        Globals.Persistent.__setstate__(self, state)
        if self.connection_string:
            try: self.connect()
            except:                
                logger.error('DABase.py __setstate__ Error connecting to relational database.',
                             exc_info=True)

    
class Connection(PooledConnection):
    manage_options=Shared.DC.ZRDB.Connection.Connection.manage_options+(
        {'label': 'Browse', 'action':'manage_browse'},
        {'label': 'Debug', 'action':'manage_trace'},
        )

    manage_browse     = HTMLFile('dtml/browse',globals())
    manage_trace      = HTMLFile('dtml/trace',globals())
    
    info = None
    def tpValues(self):
        r=[]
        c = self.getconn()
        c.registerRM()  # connection has to be returned to pool
        if c:            
            for d in c.tables(rdb=0):
                name=d['TABLE_NAME']
                b=TableBrowser()
                b.__name__=name
                b._d=d
                b._c=c
                try:
                    b.icon=table_icons[d['TABLE_TYPE']]
                except KeyError:
                    # might not be there
                    pass
                r.append(b)        
        return r

    def ZDCO2DEBUG(self, REQUEST=None):
        """Print DCOracle 2 debugging information"""
        import DCOracle2
        dbg = DCOracle2.traceback(format=0)
        return dbg

    def ZDCO2VERSION(self, REQUEST=None):
        """Print Oracle Version Table"""
        import DCOracle2
        conn = self.getconn()
        items = conn.db.Version().items()
        pool.putconn(self.connection_string, 
                     self.get_unique_id())
        return items

    

class Browser(Base):
    def __getattr__(self, name):
        try: return self._d[name]
        except KeyError: raise AttributeError, name

class values:

    def len(self): return 1

    def __getitem__(self, i):
        try: return self._d[i]
        except AttributeError: pass
        self._d=self._f()
        return self._d[i]

class TableBrowser(Browser, Acquisition.Implicit):
    icon='what'
    Description=check=''
    info=HTMLFile('dtml/table_info',globals())
    menu=HTMLFile('dtml/table_menu',globals())

    def tpValues(self):
        v=values()
        v._f=self.tpValues_
        return v

    def tpValues_(self):
        r=[]
        tname=self.__name__
        for d in self._c.columns(tname):
            b=ColumnBrowser()
            b._d=d
            try: b.icon=field_icons[d['Type']]
            except: pass
            b.TABLE_NAME=tname
            r.append(b)
        return r

    def tpId(self): return self._d['TABLE_NAME']
    def tpURL(self): return "Table/%s" % self._d['TABLE_NAME']
    def Name(self): return self._d['TABLE_NAME']
    def Type(self): return self._d['TABLE_TYPE']

    manage_designInput=HTMLFile('dtml/designInput',globals())
    def manage_buildInput(self, id, source, default, REQUEST=None):
        "Create a database method for an input form"
        args=[]
        values=[]
        names=[]
        columns=self._columns
        for i in range(len(source)):
            s=source[i]
            if s=='Null': continue
            c=columns[i]
            d=default[i]
            t=c['Type']
            n=c['Name']
            names.append(n)
            if s=='Argument':
                values.append("<!--#sql-value %s type=%s-->'" %
                              (n, vartype(t)))
                a='%s%s' % (n, boboType(t))
                if d: a="%s=%s" % (a,d)
                args.append(a)
            elif s=='Property':
                values.append("<!--#sql-value %s type=%s-->'" %
                              (n, vartype(t)))
            else:
                if isStringType(t):
                    if find(d,"\'") >= 0: d=join(split(d,"\'"),"''")
                    values.append("'%s'" % d)
                elif d:
                    values.append(str(d))
                else:
                    raise ValueError, (
                        'no default was given for <em>%s</em>' % n)




class ColumnBrowser(Browser):
    icon='field'

    def check(self):
        return ('\t<input type=checkbox name="%s.%s">' %
                (self.TABLE_NAME, self._d['Name']))
    def tpId(self): return self._d['Name']
    def tpURL(self): return "Column/%s" % self._d['Name']
    def Description(self):
        d=self._d
        if d['Scale']:
            return " %(Type)s(%(Precision)s,%(Scale)s) %(Nullable)s" % d
        elif d['Precision']:
            return " %(Type)s(%(Precision)s) %(Nullable)s" % d
        else:
            return " %(Type)s %(Nullable)s" % d

table_icons={
    'TABLE': 'table',
    'VIEW':'view',
    'SYSTEM_TABLE': 'stable',
    }

field_icons={
    'BIGINT': 'int',
    'BINARY': 'bin',
    'BIT': 'bin',
    'CHAR': 'text',
    'CHARZ': 'text',
    'DATE': 'date',
    'DECIMAL': 'float',
    'DOUBLE': 'float',
    'FLOAT': 'float',
    'INTEGER': 'int',
    'LONGVARBINARY': 'bin',
    'LONGVARCHAR': 'text',
    'NUMBER': 'float',
    'NUMERIC': 'float',
    'REAL': 'float',
    'SMALLINT': 'int',
    'STRING': 'text',
    'TIME': 'time',
    'TIMESTAMP': 'datetime',
    'TINYINT': 'int',
    'VARBINARY': 'bin',
    'VARCHAR': 'text',
    'VARCHAR2': 'text',
    }
